package cn.db101.dao;

import cn.db101.model.book;
import cn.db101.util.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class bookDao {

    private DbUtil dbUtil;
    private Connection connection;

    public ArrayList<book> getAll() {
        ArrayList<book> arrayList = new ArrayList<>();
        Statement stat;

        try {
            dbUtil = new DbUtil();
            connection = dbUtil.getCon();

            stat = connection.createStatement();
            String sql = "select *from book"; //��
            ResultSet rs = stat.executeQuery(sql);


            while (rs.next()) {
                //System.out.println("1111111111111");
                book b = new book();
                b.setBook_id(rs.getString(1));
                b.setIsbn(rs.getString(2));
                b.setBook_name(rs.getString(3));
                b.setWriter(rs.getString(4));
                b.setPublisher(rs.getString(5));
                b.setPubdate(rs.getDate(6));
                b.setBook_shelf_no(rs.getString(7));
                b.setBook_status(rs.getInt(8));
                b.setDel(rs.getInt(9));
                arrayList.add(b);
            }

            rs.close();
            stat.close();
            dbUtil.closeCon(connection);
        } catch (Exception e) {
            // TODO: handle exception
            return arrayList;
        }

        return arrayList;
    }

    public ArrayList<book> findID(String id) {
        ArrayList<book> arrayList = new ArrayList<>();
        try {
            dbUtil = new DbUtil();
            connection = dbUtil.getCon();
            String sql = "select * from book where book_id = ? "; //��

            PreparedStatement pre = connection.prepareStatement(sql);
            pre.setString(1, id);


            ResultSet rs = pre.executeQuery();

            while (rs.next()) {
                book b = new book();
                b.setBook_id(rs.getString(1));
                b.setIsbn(rs.getString(2));
                b.setBook_name(rs.getString(3));
                b.setWriter(rs.getString(4));
                b.setPublisher(rs.getString(5));
                b.setPubdate(rs.getDate(6));
                b.setBook_shelf_no(rs.getString(7));
                b.setBook_status(rs.getInt(8));
                b.setDel(rs.getInt(9));
                arrayList.add(b);
            }
            rs.close();
            pre.close();
            dbUtil.closeCon(connection);
        } catch (Exception e) {
            // TODO: handle exception
            return arrayList;
        }
        return arrayList;
    }

    public void deleteBook(int choose, String id) {

        try {
            dbUtil = new DbUtil();
            connection = dbUtil.getCon();
            String sql = null; //��
            if (choose == 0) {
                sql = "delete from book where book_name = ? ";
            } else if (choose == 1) {
                sql = "delete from book where book_id = ? ";
            } else {
                sql = "delete from book where isbn = ? ";
            }
            PreparedStatement pre = connection.prepareStatement(sql);
            pre.setString(1, id);
            pre.executeUpdate();
            pre.close();
            dbUtil.closeCon(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public ArrayList<book> findName(String name) {
        ArrayList<book> arrayList = new ArrayList<>();
        try {
            dbUtil = new DbUtil();
            connection = dbUtil.getCon();
            String sql = "select * from book where book_name = ? "; //��

            PreparedStatement pre = connection.prepareStatement(sql);
            pre.setString(1, name);


            ResultSet rs = pre.executeQuery();

            while (rs.next()) {
                book b = new book();
                b.setBook_id(rs.getString(1));
                b.setIsbn(rs.getString(2));
                b.setBook_name(rs.getString(3));
                b.setWriter(rs.getString(4));
                b.setPublisher(rs.getString(5));
                b.setPubdate(rs.getDate(6));
                b.setBook_shelf_no(rs.getString(7));
                b.setBook_status(rs.getInt(8));
                b.setDel(rs.getInt(9));
                arrayList.add(b);
            }
            rs.close();
            pre.close();
            dbUtil.closeCon(connection);
        } catch (Exception e) {
            // TODO: handle exception
            return arrayList;
        }
        return arrayList;
    }

    public ArrayList<book> findPublisher(String publisher) {
        ArrayList<book> arrayList = new ArrayList<>();
        try {
            dbUtil = new DbUtil();
            connection = dbUtil.getCon();
            String sql = "select * from book where publisher = ? "; //��

            PreparedStatement pre = connection.prepareStatement(sql);
            pre.setString(1, publisher);


            ResultSet rs = pre.executeQuery();

            while (rs.next()) {
                book b = new book();
                b.setBook_id(rs.getString(1));
                b.setIsbn(rs.getString(2));
                b.setBook_name(rs.getString(3));
                b.setWriter(rs.getString(4));
                b.setPublisher(rs.getString(5));
                b.setPubdate(rs.getDate(6));
                b.setBook_shelf_no(rs.getString(7));
                b.setBook_status(rs.getInt(8));
                b.setDel(rs.getInt(9));
                arrayList.add(b);
            }
            rs.close();
            pre.close();
            dbUtil.closeCon(connection);
        } catch (Exception e) {
            // TODO: handle exception
            return arrayList;
        }
        return arrayList;
    }

    public ArrayList<book> findWriter(String writer) {
        ArrayList<book> arrayList = new ArrayList<>();
        try {
            dbUtil = new DbUtil();
            connection = dbUtil.getCon();
            String sql = "select * from book where writer = ? "; //��

            PreparedStatement pre = connection.prepareStatement(sql);
            pre.setString(1, writer);


            ResultSet rs = pre.executeQuery();

            while (rs.next()) {
                book b = new book();
                b.setBook_id(rs.getString(1));
                b.setIsbn(rs.getString(2));
                b.setBook_name(rs.getString(3));
                b.setWriter(rs.getString(4));
                b.setPublisher(rs.getString(5));
                b.setPubdate(rs.getDate(6));
                b.setBook_shelf_no(rs.getString(7));
                b.setBook_status(rs.getInt(8));
                b.setDel(rs.getInt(9));
                arrayList.add(b);
            }
            rs.close();
            pre.close();
            dbUtil.closeCon(connection);
        } catch (Exception e) {
            // TODO: handle exception
            return arrayList;
        }
        return arrayList;
    }

    public ArrayList<book> findISBN(String isbn) {
        ArrayList<book> arrayList = new ArrayList<>();
        try {
            dbUtil = new DbUtil();
            connection = dbUtil.getCon();
            String sql = "select * from book where isbn = ? "; //��

            PreparedStatement pre = connection.prepareStatement(sql);
            pre.setString(1, isbn);


            ResultSet rs = pre.executeQuery();

            while (rs.next()) {
                book b = new book();
                b.setBook_id(rs.getString(1));
                b.setIsbn(rs.getString(2));
                b.setBook_name(rs.getString(3));
                b.setWriter(rs.getString(4));
                b.setPublisher(rs.getString(5));
                b.setPubdate(rs.getDate(6));
                b.setBook_shelf_no(rs.getString(7));
                b.setBook_status(rs.getInt(8));
                b.setDel(rs.getInt(9));
                arrayList.add(b);
            }
            rs.close();
            pre.close();
            dbUtil.closeCon(connection);
        } catch (Exception e) {
            // TODO: handle exception
            return arrayList;
        }
        return arrayList;
    }

    public int addBookInfro(book newb) {
        int ok = 0;
        try {
            dbUtil = new DbUtil();
            connection = dbUtil.getCon();
            String sql = "insert into book values(?,?,?,?,?,?,?,?,?) "; //��

            PreparedStatement pre = connection.prepareStatement(sql);
            pre.setString(1, newb.getBook_id());
            pre.setString(2, newb.getIsbn());
            pre.setString(3, newb.getBook_name());
            pre.setString(4, newb.getWriter());
            pre.setString(5, newb.getPublisher());
            pre.setDate(6, newb.getPubdate());
            pre.setString(7, newb.getBook_shelf_no());
            pre.setInt(8, newb.getBook_status());
            pre.setInt(9, newb.getDel());

            ok = pre.executeUpdate();


            pre.close();
            dbUtil.closeCon(connection);
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        return ok;
    }

    public static void main(String[] args) {
        new bookDao().deleteBook(1, "9");
        if (new bookDao().returnBookDao("8", "201824100456") == 1) {
            System.out.println("success");
        } else {
            System.out.println("failed");
        }
    }

    /**
     * ���ļ�¼
     *
     * @param con
     * @return
     * @throws Exception
     */
    public static ResultSet record(Connection con) throws Exception {
        String sql = "select book_name,reader_no,borrow_start_time,return_time from book,return_book where book.book_id=return_book.book_id";
        PreparedStatement pstmt = con.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        return rs;
    }


    public book bookInfo(String book_id) {

        book newbook = null;
        try {
            dbUtil = new DbUtil();
            connection = dbUtil.getCon();
            String sql = "select book_name,book_status from book where book_id=? and del<>1";
            PreparedStatement pre = connection.prepareStatement(sql);
            pre.setString(1, book_id);
            ResultSet rs = pre.executeQuery();

            newbook = new book();
            newbook.setBook_id(book_id);
            if (rs.next()) {
                newbook.setBook_name(rs.getString(1));
                newbook.setBook_status(rs.getInt(2));
            } else {
                return null;
            }
            rs.close();
            pre.close();
            dbUtil.closeCon(connection);

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        return newbook;

    }

    public boolean alterBookInfo(book newbook) {

        try {
            dbUtil = new DbUtil();
            connection = dbUtil.getCon();
            String sqltest = "select * from book where book_id=? and del<>1";
            PreparedStatement pretest = connection.prepareStatement(sqltest);
            pretest.setString(1, newbook.getBook_id());
            ResultSet rs = pretest.executeQuery();
            if (!rs.next()) {
                return false;
            }

            if (newbook.getBook_name() != null) {
                String sql = "update book set book_name='" + newbook.getBook_name() + "' where book_id='" + newbook.getBook_id() + "'";
                PreparedStatement pre = connection.prepareStatement(sql);
                pre.execute();
            }
            if (newbook.getBook_shelf_no() != null) {
                String sql = "update book set book_shelf_no='" + newbook.getBook_shelf_no() + "' where book_id='" + newbook.getBook_id() + "'";
                PreparedStatement pre = connection.prepareStatement(sql);
                pre.execute();
            }
            if (newbook.getIsbn() != null) {
                String sql = "update book set isbn='" + newbook.getIsbn() + "' where book_id='" + newbook.getBook_id() + "'";
                //String sql = "update book set isbn=? where book_id=?";
                PreparedStatement pre = connection.prepareStatement(sql);
//					pre.setString(1, newbook.getIsbn());
//					pre.setString(2, newbook.getBook_id());
                pre.execute();
            }
            if (newbook.getWriter() != null) {
                String sql = "update book set writer='" + newbook.getWriter() + "' where book_id='" + newbook.getBook_id() + "'";
                PreparedStatement pre = connection.prepareStatement(sql);
                pre.execute();
            }
            if (newbook.getPublisher() != null) {
                String sql = "update book set publisher='" + newbook.getPublisher() + "' where book_id='" + newbook.getBook_id() + "'";
                PreparedStatement pre = connection.prepareStatement(sql);
                pre.execute();
            }

            String sql = "update book set del='" + newbook.getDel() + "', book_status='" + newbook.getBook_status() + "' where book_id='" + newbook.getBook_id() + "'";
            PreparedStatement pre = connection.prepareStatement(sql);
//				pre.setString(1, book_id);
            pre.execute();

//				rs.close();
            pre.close();
            dbUtil.closeCon(connection);
            return true;
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
            return false;
        }
    }

    public int borrowBookDao(String book_id, String reader_id) {
        int isBorrow, isDel;
        try {
            dbUtil = new DbUtil();
            connection = dbUtil.getCon();
            String sql = "select * from book where book_id=? and del<>1";
            PreparedStatement pretest = connection.prepareStatement(sql);
            pretest.setString(1, book_id);
            ResultSet rs = pretest.executeQuery();
            if (!rs.next()) {
                return 0;
            } else {
                isBorrow = Integer.parseInt(rs.getString(8));
                isDel = Integer.parseInt(rs.getString(9));
                if (isBorrow == 1 || isDel == 1) {
                    return -1;
                }
            }
            sql = "select * from reader where reader_no=? and del<>1";
            pretest = connection.prepareStatement(sql);
            pretest.setString(1, reader_id);
            rs = pretest.executeQuery();
            int borrownum = 0;
            if (!rs.next()) {
                return 0;
            } else {
                borrownum = Integer.parseInt(rs.getString(7));
                isDel = Integer.parseInt(rs.getString(9));
                if (borrownum <= 0 || isDel == 1) {
                    return -2;
                }
            }

            // 插入借书表，并同步更新数量的状态
            java.util.Date date = new java.util.Date();
            java.sql.Date sql_date = new java.sql.Date(date.getTime());

            sql = "insert into borrow(book_id,reader_no,borrow_start_time,may_borrow_days) values(?, ?, ?, ?)";
            pretest = connection.prepareStatement(sql);
            pretest.setString(1, book_id);
            pretest.setString(2, reader_id);

            pretest.setDate(3, sql_date);
            pretest.setInt(4, 7);
            int result = pretest.executeUpdate();

            // 更新借书状态
            sql = "update book set book_status = 1 where book_id = ?";
            pretest = connection.prepareStatement(sql);
            pretest.setString(1, book_id);
            pretest.executeUpdate();

            pretest.close();
            dbUtil.closeCon(connection);
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }

    public int returnBookDao(String book_id, String reader_id) {

        int isBorrow, isDel;
        try {
            dbUtil = new DbUtil();
            connection = dbUtil.getCon();
            String sql = "select * from book where book_id=? and del<>1";
            PreparedStatement pretest = connection.prepareStatement(sql);
            pretest.setString(1, book_id);
            ResultSet rs = pretest.executeQuery();
            if (!rs.next()) {
                return 0;
            } else {
                isBorrow = Integer.parseInt(rs.getString(8));
                isDel = Integer.parseInt(rs.getString(9));
                if (isBorrow == 0 || isDel == 1) {
                    return -1;
                }
            }
            sql = "select * from reader where reader_no=? and del<>1";
            pretest = connection.prepareStatement(sql);
            pretest.setString(1, reader_id);
            rs = pretest.executeQuery();
            int borrownum = 0;
            if (!rs.next()) {
                return 0;
            } else {
                borrownum = Integer.parseInt(rs.getString(5));
                isDel = Integer.parseInt(rs.getString(9));
                if (borrownum <= 0 || isDel == 1) {
                    return -2;
                }
            }

            String sql1 = "select return_id from `return_book`";
            PreparedStatement pre1 = connection.prepareStatement(sql1);
            rs = pre1.executeQuery();
            int returnid = -1;
            while (rs.next()) {
                if (Integer.parseInt(rs.getString(1)) + 1 > returnid) {
                    returnid = Integer.parseInt(rs.getString(1)) + 1;
                }
            }
            if (returnid == -1) {
                return 0;
            }


            sql = "insert into return_book(return_id, book_id, reader_no) values(?, ?, ?)";
            pretest = connection.prepareStatement(sql);
            pretest.setString(1, String.valueOf(returnid));
            pretest.setString(2, book_id);
            pretest.setString(3, reader_id);
            int result = pretest.executeUpdate();
            pretest.close();
            dbUtil.closeCon(connection);
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }

    }

}
